<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "java.util.Properties" %>
<%@ page import = "javax.servlet.ServletContext" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.InputStream" %>
<%@ page import = "java.sql.PreparedStatement" %>
<%@page import="java.util.regex.Matcher"%>
<%@page import="java.util.regex.Pattern"%>

<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
		<title>Add Course</title>
	<link rel="stylesheet" type="text/css" href="css/stylev2.css" />
	<link rel="stylesheet" href="css/validation.css" />
	<link rel="stylesheet" href="css/jquery.mobile-1.3.2.min.css"/>
	<link rel="stylesheet" type="text/css" href="css/table-no-header.css" />
    
	<script type="text/javascript" src="js/livevalidation_standalone.js"></script> 
	<script type="text/javascript" src="js/jquery-2.1.1.min.js"></script>
	
	<style type="text/css">
		a {
	      text-decoration:none;
	   }
	</style>
	
</head>
<body>
<script>
$(document).ready(function(){

	$("#frm_add_manual").hide();
	$("#frm_add_file").hide();
	
	 $("#btn_add_file").click(function(){
	 
		 
			$("#frm_add_manual").hide();
			$("#frm_add_file").show();
	 });
	 
	 $("#btn_add_manual").click(function(){
	 	
		 $("#frm_add_file").hide();
		$("#frm_add_manual").show();
			
		 
	 });
	
	
	
});

function isValidUploadForm(oForm){
	
	//alert("Active isValidUploadForm");
	
	var _validFileExtensions = [".xlsx", ".xls"];
	  var arrInputs = oForm.getElementsByTagName("input");
	 
	  
	//  alert("arrInputs:"+ oForm.getElementsByTagName("input")[0].value);
	  
	  var file_name =  oForm.getElementsByTagName("input")[0].value
	  	if(file_name=="")
	  	{
	  		alert("กรุณาเลือกไฟล์ที่จะทำการ Import ในรูปแบบ xls, xlsx");
	  		return false;
	  	}
	  
	    for (var i = 0; i < arrInputs.length; i++) {
	        var oInput = arrInputs[i];
	        if (oInput.type == "file") {
	            var sFileName = oInput.value;
	            if (sFileName.length > 0) {
	                var blnValid = false;
	                for (var j = 0; j < _validFileExtensions.length; j++) {
	                    var sCurExtension = _validFileExtensions[j];
	                    if (sFileName.substr(sFileName.length - sCurExtension.length, sCurExtension.length).toLowerCase() == sCurExtension.toLowerCase()) {
	                        blnValid = true;
	                        break;
	                    }
	                }

	                if (!blnValid) {
	                    alert("เกิดข้อผิดพลาด " + sFileName + " ไฟล์ที่แนบไม่ตรงรูปแบบของ : " + _validFileExtensions.join(", "));
	                    return false;
	                }
	            }
	        }
	    }

	    return true;
	
	
	
}


function search_name(courseid) {
	if($('#namelist').find('option').length > 0) {
		
		$('#namelist').find('option').remove();
	}
	if(courseid.length>2) {
		
		/* AJAX */
		var xmlhttp;
		
		if(window.XMLHttpRequest) {
			// code for IE7+, Firefox, Chrome, Opera, Safari
			xmlhttp = new XMLHttpRequest();
		}
		else {
			// code for IE6, IE5
			xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
		}
		
		xmlhttp.onreadystatechange = function() {
			if(xmlhttp.readyState == 4 && xmlhttp.status == 200) {
				
				
				var jsonObj = JSON.parse(xmlhttp.responseText);
				
				if(jsonObj.length == 0) {
					// no data
				}
				else{
					
					for(i in jsonObj) {
						var text = jsonObj[i].courseId+" , "+jsonObj[i].courseNameEN+" , "+jsonObj[i].credit+" ( "+jsonObj[i].lecture+" "+jsonObj[i].lab+" "+jsonObj[i].selfLearn+" ) , "+jsonObj[i].details;
						var option = '<option value="'+text+'" >';
						$('#namelist').append(option);								
					}
					var result = document.getElementById("search_box").value;	
					
				}
			}
			
		}
		
		xmlhttp.open("POST", "curriculum_search_course_name.jsp?courseid="+courseid, true);
		xmlhttp.send();
		
	}	
}
</script>	

<%
	//Get session
	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
			
	if (htp_session == null || htp_session.getAttribute("sUser") == null) {
	    // Forward the control to login.jsp if authentication fails or session expires
	    request.getRequestDispatcher("/login.jsp").forward(request,
	        response);
	}
	
	if("admin".equals(htp_session.getAttribute("sUser"))){%>
	<script>alert("This user dont have PERMISSION to access this zone.");</script>
	<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=admin_home.jsp"> 	
	<%}
	
	//GET PERMISSION

		String name = String.valueOf(session.getAttribute("name"));
		String surname = String.valueOf(session.getAttribute("surname"));
		String role_name = String.valueOf(session.getAttribute("curriculum_role"));
		
		String department_id = String.valueOf(session.getAttribute("department_id"));
				
		System.out.println("name : " + name);///////////////////////////////
		System.out.println("department_id : " + department_id);/////////////////////////
		
		List<String> system_name = new ArrayList<String>();
		
		Permission permission = new Permission(strUser,name);
		permission.setCurriculumCreate(Boolean.valueOf(String.valueOf(session.getAttribute("curriculum_create"))));
		permission.setCurriculumDelete(Boolean.valueOf(String.valueOf(session.getAttribute("curriculum_delete"))));
		permission.setCurriculumRead(Boolean.valueOf(String.valueOf(session.getAttribute("curriculum_read"))));
		permission.setCurriculumUpdate(Boolean.valueOf(String.valueOf(session.getAttribute("curriculum_update"))));
		
		String permiss = "";
		if(permission.getCurriculumCreate()) {
			permiss = permiss + "Add";
		}
		if(permission.getCurriculumUpdate()) {
			if("".equals(permiss)) {
				permiss = permiss + "Edit";
			} else {
				permiss = permiss + " Edit";
			}
		}
		if(permission.getCurriculumRead()) {
			if("".equals(permiss)) {
				permiss = permiss + "Read";
			} else {
				permiss = permiss + " Read";
			}
		}
		if(permission.getCurriculumDelete()) {
			if("".equals(permiss)) {
				permiss = permiss + "Delete";
			} else {
				permiss = permiss + " Delete";
			}
		}
		
	///////////////////////////////////////////////////////////////////////////////////////////////////////////			
	//check permission
		if(!permission.getCurriculumCreate()){%>
			<script>alert("This user dont have PERMISSION to access this zone.");</script>
			<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=curriculum_home.jsp"> 	
		<%}
	
	//set Database Connection
		String hostProps = "";
		String usernameProps  = "";
		String passwordProps  = "";
		String databaseProps = "";
	
		try {
			//get current path
			ServletContext servletContext = request.getSession().getServletContext();
			
			InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
			Properties props = new Properties();
			
			props.load(input);

			hostProps  = props.getProperty("host");
			usernameProps  = props.getProperty("username");
			passwordProps  = props.getProperty("password");
			databaseProps = props.getProperty("database");
		} catch (Exception e) { 
			out.println(e);  
		}
		
		System.out.println("hostProperties : "+hostProps);////////////////try/////////////////
		System.out.println("usernameProperties : "+usernameProps);////////////////try/////////////////
		System.out.println("passwordProperties : "+passwordProps);////////////////try/////////////////
		System.out.println("databaseProperties : "+databaseProps);////////////////try/////////////////
		// connect database
		Connection connect = null;		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		
			connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
					"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620");//////"&characterEncoding=tis620" Encoding Thai);
		
			if(connect != null){
				System.out.println("Database Connect Sucesses.");
			} else {
				System.out.println("Database Connect Failed.");	
			}

		} catch (Exception e) {
			out.println(e.getMessage());
			e.printStackTrace();
		}
		
		/* find department in database */
		List<Department> department = new ArrayList<Department>();
		
		try {			   
			ResultSet rs_department = connect.createStatement().executeQuery("SELECT department_id, department_name_th " 
					+ " FROM department " + " ORDER BY department_id");
			
			while(rs_department.next()) {
				Department dep = new Department();
				
				dep.setDepartmentNameTH(rs_department.getString("department_name_th"));
				dep.setDepartmentId(Integer.parseInt(rs_department.getString("department_id")));
				
				department.add(dep);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		

%>
	<div id="page-wrap">
		<div id="inside">
			<table border="0">
				<tr style="font-size:24px">
					<td width="600" height="100" style= "background-color: #273b80;border: 0px ;padding: 0px; 0px;" >
						<div id="header_banner-2"></div>
					</td>
					<td width="680" height="100" style= "background-color: #273b80;border: 0px;padding: 0px; 0px;" >
						<p align="right" ><label><font size="4" color="#FFFFFF">  username : <%=strUser %>&nbsp;&nbsp;</font></label></p>
						<a href="logout.jsp"><p align="right"><label style="cursor: pointer;"><font size="3" color="#ffffff">log out &nbsp;</font></label></p></a>
					</td>							
				</tr>
			</table>
		    <table border="0">
						<tr style="font-size:10px">
							<td width = "720" height="50" style= "background-color:#D0D0D0 ;">
								<center><a href="curriculum_home.jsp"><h2><label style="cursor: pointer;">หน้าหลัก </label></h2></center>
							</td>
<%
			if(permission.getCurriculumRead()) {
%>	
							<td  width = "720" height="50" style= "background-color:#D0D0D0 ;">
								<center><a href="curriculum_search.jsp"><h2><label style="cursor: pointer;">ค้นหา</label></h2></a></center>
							</td>
<%
			}
			if(permission.getCurriculumCreate()) {
%>
							<td width = "720" height="50" style= "background-color:#FFFFFF; border-bottom-color: #FFFFFF; border-left-color: #FFFFFF;">
								<center><a href="curriculum_import.jsp"><h2><label>เพิ่มรายวิชา</label></h2></a></center>
							</td>
							<td  width = "720" height="50" style= "background-color:#D0D0D0 ;">
								<center><a href="curriculum_add_curriculum.jsp"><h2><label style="cursor: pointer;">เพิ่มหลักสูตรใหม่</label></h2></a></center>
							</td>
							<td  width = "720" height="50" style= "background-color:#D0D0D0 ;">
								<center><a href="curriculum_modified_home.jsp"><h2><label style="cursor: pointer;">ปรับปรุงหลักสูตร</label></h2></a></center>
							</td>
<%
			}
			if(permission.getCurriculumUpdate()) {
%>
							<td  width = "720" height="50" style= "background-color:#D0D0D0 ;">
								<center><a href="curriculum_edit_home.jsp"><h2><label style="cursor: pointer;">แก้ไขข้อมูลหลักสูตร</label></h2></a></center>
							</td>
<%
			}
%>
							
						</tr>
			</table>
			
		<div id="main-content-ac">
		<!-- 	<h2 style="padding-left: 2.5cm; color: #273b80;">เพิ่มข้อมูลรายวิชา</h2>	 -->
		
			<br><br><br><br>
			<center>
						<table>
							
							<tr>
								<td>
									<button name="add_file" id="btn_add_file" class="buttonStyle" style= "height: 50px; width: 150px; font-size:medium;">เพิ่มรายวิชาจากไฟล์  Microsoft Excel</button>
								</td>							
								<td>	
									<button name="add_manual" id="btn_add_manual" class="buttonStyle" style= "height:50px; width: 150px; font-size:medium;">เพิ่มทีละรายวิชา</button>
								</td>	
							</tr>
						</table>
						<br><br>
								<table>
									<tr style="font-size:24px">
										<td height="50">
											<font size="5"><strong>ค้นหาวิชา: </strong></font>
											<label><font size="5" color="#FF0000"></font></label>
										</td>
										<td>
											<input type="text" name="search_box" autocomplete="off" placeholder="ex. egco1" id="search_box" required style="height: 25px; width: 400px; font-size:medium;" value="" list="namelist" onkeyup="search_name(this.value)" />
											<datalist id="namelist"></datalist>							
										</td>
										
									</tr>
								</table>
							
					</center>
					<br><br>
			<center>
<%/////////////////////ADD MANUAL/////////////////////////////////////////////////////////////////	%>									
			<div id="frm_add_manual">
			<table border="0">
				<form id="form1" name="form1" method="post" action="">					
				       <tbody>				            	
				             	<tr style="font-size:24px">
									<td width="500" height="50">
										<label for="labelcourseID"><font size="5"><strong>รหัสของวิชา (ex. EGCO111)</strong></font></label>
										<label><font size="5" color="#FF0000">*</font></label>
									</td>
									<td width="500" height="50">
										<input id="courseID" name="courseID" maxlength="7" required type="text" style="font-size:18px"/>
									</td>
								</tr>
								<!-- 
								<tr style="font-size:24px">
									<td width="500" height="50">
										<label for="labelcoursenameTH"><font size="5"><strong>ชื่อวิชา (ภาษาไทย)</strong></font></label>
										<label><font size="5" color="#FF0000">*</font></label>
									</td>
									<td width="500" height="50">
										<input id="coursenameTH" name="coursenameTH" type="text" required style="font-size:18px" />
									</td>
								</tr>
								-->
								<tr style="font-size:24px">
									<td width="500" height="50">
										<label for="labelcoursenameEN"><font size="5"><strong>ชื่อวิชา (ภาษาอังกฤษ)</strong></font></label>
										<label><font size="5" color="#FF0000">*</font></label>
									</td>
									<td width="500" height="50">
										<input id="coursenameEN" name="coursenameEN" type="text" required style="font-size:18px" />
									</td>
								</tr>
								
								<tr style="font-size:24px">
									<td width="500" height="50">
										<label for="labelcourseCredit"><font size="5"><strong>หน่วยกิตของวิชา</strong></font></label>
										<label><font size="5" color="#FF0000">*</font></label>
									</td>
									<td width="500" height="50">
										<input id="courseCredit" name="courseCredit" type="text" maxlength="3" required style="font-size:18px" />
									</td>
								</tr>
																
								<tr style="font-size:24px">
									<td width="500" height="50">
										<label for="labellecture"><font size="5"><strong>บรรยาย</strong></font></label>
										<label><font size="5" color="#FF0000">*</font></label>
									</td>
									<td width="500" height="50">
										<input id="lecture" name="lecture" type="text" maxlength="2" required style="font-size:18px" />
									</td>
								</tr>
								
								<tr style="font-size:24px">
									<td width="500" height="50">
										<label for="labellab"><font size="5"><strong>ปฏิบัติ</strong></font></label>
										<label><font size="5" color="#FF0000">*</font></label>
									</td>
									<td width="500" height="50">
										<input id="lab" name="lab" type="text" maxlength="2" required style="font-size:18px" />
									</td>
								</tr>
								
								<tr style="font-size:24px">
									<td width="500" height="50">
										<label for="labelself"><font size="5"><strong>ศึกษาด้วยตนเอง</strong></font></label>
										<label><font size="5" color="#FF0000">*</font></label>
									</td>
									<td width="500" height="50">
										<input id="self" name="self" type="text" maxlength="2" required style="font-size:18px" />
									</td>
								</tr>	
								<!--
								<tr style="font-size:24px;">
									<td width="500" height="50">
										<label for="labeldetails"><font size="5"><strong>รายละเอียดของวิชา</strong></font></label>
									</td>
									<td width="500" height="50">
										<textarea name="detail" rows="4" cols="50"></textarea>
										
									</td>
								</tr>	           
				             -->
				             <tr>
								<td>
									<center><input type="submit" value="save" name="save"class="buttonStyle"/></center>																			
							
								</td>									
							
							
					</form>			
								<td>
									<center><a href="curriculum_home.jsp"><button class="buttonStyle">cancel</button></a></center>							
								</td>
							</tr>
							</tbody>	
					</table>
				</div>
<%////////////////////ADD FILE/////////////////////////////////////////////////////////////////////////////	%>									
											
				<div id="frm_add_file">
					<table>
						<tr>
							<td>
								<center>
									 <form method="post" onsubmit="return isValidUploadForm(this);" action="UploadCurriServlet" enctype="multipart/form-data">
			        		  			 <font size="4">กรุณาเลือกรายวิชาที่ต้องการอัพโหลด:  </font><input id="file_box" type="file" name="uploadFile"> 
			        		  			 <br>
			        		  			 <br>
			        		  			 <br>
			        		  			 
			        		  			 <input type="submit" value="Upload" class="buttonStyle"/>
			        		  			 
			        		  			 <br>
			        		  		</form>
			        		  			 
			        		  	</center>
			        		 </td>
			        		 
	        		  		 
						</tr>
							
					</table>
				</div>		
							
					</center>			
						<br/><br/><br/><br/>
				
				
			<div style="clear: both;"></div>
				
			<div id="footer"></div>
			
		</div>		
			<div style="clear: both;"></div>		
   </div>
</div>

<%
		String checkButton = request.getParameter("save");
		System.out.println(checkButton);
		
		// check save button is pressed
		if("save".equals(checkButton)) {	
			
				request.setCharacterEncoding("UTF-8");
								
				// get value from tag
				String courseID = new String(request.getParameter("courseID").getBytes("ISO8859_1"),"UTF-8").toUpperCase();
				//String coursenameTH = new String(request.getParameter("coursenameTH").getBytes("ISO8859_1"),"UTF-8");
				String coursenameEN = new String(request.getParameter("coursenameEN").getBytes("ISO8859_1"),"UTF-8");
				String courseCredit = request.getParameter("courseCredit");
				String lecture = request.getParameter("lecture");
				String lab = request.getParameter("lab");
				String self = request.getParameter("self");				
				//String detail = new String(request.getParameter("detail").getBytes("ISO8859_1"),"UTF-8");
				//System.out.println("detail: "+detail);
				
//////////////////////////////CHECK IF INPUT WERE SPECIAL CHARACTERS> //////////////////////
				Pattern regex = Pattern.compile("[$&+,:;=?@#^*')(|!%]");
				Matcher matchercourseID = regex.matcher(courseID);
				//Matcher matchercoursenameTH = regex.matcher(coursenameTH);
				Matcher matchercoursenameEN = regex.matcher(coursenameEN);
				Matcher matchercourseCredit = regex.matcher(courseCredit);
				Matcher matcherlecture = regex.matcher(lecture);
				Matcher matcherlab = regex.matcher(lab);
				Matcher matcherself = regex.matcher(self);
				//Matcher matcherdetail = regex.matcher(detail);
				
				if (matchercourseID.find() || /*matchercoursenameTH.find() ||*/ matchercoursenameEN.find() || matchercourseCredit.find() || matcherlecture.find() || matcherlab.find() || matcherself.find()){
					%>
									<script language="javascript"> alert("ข้อมูลไม่ถูกต้อง โปรดใช้ตัวเลขหรือตัวอักษรเท่านั้น");	</script>
					<%}else{
							 if(!courseCredit.matches("[-+]?\\d+(\\.\\d+)?")){%>
							<script>alert("กรุณาใส่จำนวนหน่วยกิตให้ถูกต้อง");</script>
		<%					}
							else if(!lecture.matches("[-+]?\\d+(\\.\\d+)?")){%>						
							<script>alert("กรุณาใส่บรรยายให้ถูกต้อง");</script>
		<%					}
							else if(!lab.matches("[-+]?\\d+(\\.\\d+)?")){%>
							<script>alert("กรุณาใส่ปฏิบัติให้ถูกต้อง");</script>
		<%					}
							else if(!self.matches("[-+]?\\d+(\\.\\d+)?")){%>
							<script>alert("กรุณาใส่ศึกษาด้วยตนเองให้ถูกต้อง");</script>
		<%					}
							else{
								///////////////////Phase 1 // Check Existing data in course table 
									ResultSet rs_course =	connect.createStatement().executeQuery(" SELECT * "
																									+ " FROM course_name "
																									+ " WHERE course_id='" + courseID +"' AND course_name_en='"+ coursenameEN + "' AND credit='"+ courseCredit +"' AND lecture_hour="+ lecture +" AND lab_hour="+ lab +" AND self_learning_hour="+ self);
									rs_course.last();
									if(rs_course.getRow()==1)
									{%>
										<script>alert("This course already exist in course_name table");</script>
									<%
										System.out.println("This course already exist in course_name table");
										
										}else{
												try {			   
													  PreparedStatement pstmt = connect.prepareStatement("INSERT INTO `course_name`(`course_id`, `course_name_en`, `credit`, `lecture_hour`, `lab_hour`, `self_learning_hour`, `detail`)" 
																+ " VALUES ('"+ courseID +"','"+ coursenameEN +"',"+ courseCredit +","+ lecture +","+ lab +","+ self +",'')" , Statement.RETURN_GENERATED_KEYS);  
													 	pstmt.executeUpdate();  
													 	
													 	String sql_course_name = "INSERT INTO `course_name`(`course_id`,`course_name_en`, `credit`, `lecture_hour`, `lab_hour`, `self_learning_hour`, `detail`)" 
																+ " VALUES ('"+ courseID +"','"+ coursenameEN +"',"+ courseCredit +","+ lecture +","+ lab +","+ self +",'')";
														
														/* Log file */
														String log_user = strUser+" add course_name";
														Log.writeUserFile(log_user);
														Log.writeUserFile(sql_course_name);
																								
														System.out.println("Phase 1 Success with courseID :"+ courseID);
														//System.out.println("Phase 2 Success with coursenameTH :"+ coursenameTH);
														System.out.println("Phase 3 Success with coursenameEN :"+ coursenameEN);
														System.out.println("Phase 4 Success with courseCredit :"+ courseCredit);
														System.out.println("Phase 5 Success with lecture :"+ lecture);
														System.out.println("Phase 6 Success with lab :"+ lab);
														System.out.println("Phase 7 Success with self :"+ self);
														//System.out.println("Phase 8 Success with detail :"+ detail);
				
						%>
													
						
														<script language="javascript"> alert(" บันทึกข้อมูลสำเร็จ");</script>	
														<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=curriculum_import.jsp"> 
						<%
													} catch (SQLException e) {
														System.out.println("Error At Phase 1 Add CurriID:"+e);
														e.printStackTrace();
													}
										}
							}
				
			}////////else from check special char
		}
		
		connect.close();
%>  
</body>
</html>